﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Word;
using ScriptDumper.Manager;
using ScriptsGenerator.Helper;

namespace ScriptDumper
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private Microsoft.Office.Interop.Word.ApplicationClass WordApp = new Microsoft.Office.Interop.Word.ApplicationClass();
        private object missing = System.Reflection.Missing.Value;

        private Microsoft.Office.Interop.Word.Document _wordDocument = null;
        private Microsoft.Office.Interop.Word.Document WorkDocument
        {
            get
            {
                return _wordDocument;
            }
            set
            {
                _wordDocument = value;
            }
        }

        private int ProgressBarValue
        {
            set
            {
                toolStripProgressBar.Value = value;
            }
        }

        private string[] TablesName
        {
            get
            {
                string[] tablesName = new string[0];
                if (!string.IsNullOrEmpty(tbExportingTables.Text))
                {
                    tablesName = tbExportingTables.Text.ToUpper().Replace(" ", "").Split(new char[] { ',' });
                    Array.Sort(tablesName);
                    return tablesName;
                }

                return tablesName;
            }
        }

        private void btnPickFile_Click(object sender, EventArgs e)
        {
            if (ofdOpenDocDialog.ShowDialog(this) == System.Windows.Forms.DialogResult.Cancel)
            {
                // 使用者沒有選檔案
                return;
            }

            tbDocName.Text = ofdOpenDocDialog.FileName; // 選擇的完整路徑

            if (string.IsNullOrEmpty(tbExportingFilename.Text) &&
                !string.IsNullOrEmpty(tbDocName.Text))
                tbExportingFilename.Text = tbDocName.Text.Substring(0, tbDocName.Text.LastIndexOf('.')) + ".sql";
        }

        private static string TrimEnd(string value)
        {
            if (string.IsNullOrEmpty(value))
                return string.Empty;
            return value.TrimEnd('\a', '\r');
        }

        private void btnStart_Click(object sender, EventArgs e)
        {
            Stopwatch stopWatch = new Stopwatch();
            stopWatch.Start();
            
            object fileName = tbDocName.Text;
            object readOnly = false;

            object isVisible = false;
            object missing = System.Reflection.Missing.Value;
            WordApp.Visible = false;

            StringBuilder sb = new StringBuilder();
            try
            {
                _wordDocument = WordApp.Documents.Open(ref fileName, ref missing, ref readOnly, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref isVisible, ref missing, ref missing, ref missing, ref missing);
                ProgressBarValue = 5;

                Dictionary<string, TableProfile> tableSchemas = DocumentHelper.GetTableProfiles(_wordDocument);
                ProgressBarValue = 10;

                foreach (TableProfile schema in tableSchemas.Values)
                {
                    DbTable dbTable = new DbTable();
                    dbTable.TableName = schema.TableName;

                    Table table = schema.SchemaTable;

                    dbTable.TableName = schema.TableName;
                    dbTable.ContainsLog = schema.ContainsLog;

                    //List<DbColumn> columns = new List<DbColumn>();

                    Dictionary<string, DbColumn> columns = new Dictionary<string, DbColumn>();

                    for (int j = 2; j <= table.Rows.Count; j++)
                    {
                        Row row = table.Rows[j];

                        DbColumn column = new DbColumn();

                        try
                        {
                            string pk = row.Cells[1].Range.Text.TrimEnd('\a', '\r');
                            string length = row.Cells[4].Range.Text.TrimEnd('\a', '\r');
                            string columnTupe = row.Cells[3].Range.Text.TrimEnd('\a', '\r');
                            string name = row.Cells[2].Range.Text.TrimEnd('\a', '\r');

                            if (!pk.Contains("-"))
                            {
                                if (!string.IsNullOrEmpty(pk))
                                {
                                    column.PkSequence = Convert.ToInt16(pk);
                                }
                            }
                            column.Name = row.Cells[2].Range.Text.TrimEnd('\a', '\r');
                            column.ColumnType = row.Cells[3].Range.Text.TrimEnd('\a', '\r');

                            if (string.IsNullOrEmpty(column.ColumnType))
                                continue;

                            if (!string.IsNullOrEmpty(length))
                            {
                                if ("N".Equals(column.ColumnType))
                                {
                                    string text = Convert.ToString(length);

                                    if (text.Contains(","))
                                    {
                                        text = text.Replace(")", "").Replace("(", "");

                                        int maxLength = Convert.ToInt16(text.Split(',')[0]);
                                        int minLength = Convert.ToInt16(text.Split(',')[1]);
                                        column.MaxLength = maxLength;
                                        column.MinLength = minLength;
                                    }
                                    else
                                    {
                                        column.MaxLength = Convert.ToInt16(text);
                                    }
                                }
                                else if ("TX".Equals(column.ColumnType))
                                {

                                }
                                else
                                {
                                    column.MaxLength = Convert.ToInt16(length);
                                }
                            }

                            column.Required = row.Cells[5].Range.Text.TrimEnd('\a', '\r') == "N" ? true : false;

                            if ("N".Equals(column.ColumnType))
                            {
                                column.DefaultValue = "0";
                            }
                            else
                            {
                                column.DefaultValue = row.Cells[6].Range.Text.TrimEnd('\a', '\r');
                            }
                        }
                        catch (Exception ex)
                        {
                            column.ErrorMessage = ex.Message;
                        }

                        columns.Add(column.Name, column);
                    }

                    dbTable.DbColumns = columns;

                    if (string.IsNullOrEmpty(tbExportingTables.Text))
                    {
                        string result = SqlScriptManager.GetInstance().GetSqlScript(dbTable);
                        sb.Append(result + Environment.NewLine);
                    }
                    else
                    {
                        if (Array.BinarySearch(TablesName, dbTable.TableName) >= 0)
                        {
                            string result = SqlScriptManager.GetInstance().GetSqlScript(dbTable);
                            sb.Append(result + Environment.NewLine);
                        }
                    }
                }

                ProgressBarValue = 50;
            }
            finally
            {
                _wordDocument.Close(ref missing, ref missing, ref missing);
                WordApp.Quit(ref missing, ref missing, ref missing);
            }

            WordApp = null;

            stopWatch.Stop();
            
            // Get the elapsed time as a TimeSpan value.
            TimeSpan ts = stopWatch.Elapsed;

            // Format and display the TimeSpan value.
            string elapsedTime = String.Format("Total Cost={0:00}:{1:00}:{2:00}.{3:00}",
                ts.Hours, ts.Minutes, ts.Seconds,
                ts.Milliseconds / 10);

            lbltoolStripStatus.Text = elapsedTime;

            tbMessage.Text = sb.ToString();

            if (!string.IsNullOrEmpty(tbExportingFilename.Text))
            {
                System.IO.FileStream fs = null;
                try
                {
                    if (string.IsNullOrEmpty(sfdExportingFilename.FileName))
                    {
                        sfdExportingFilename.FileName = tbExportingFilename.Text;
                    }

                    using (fs = (System.IO.FileStream)sfdExportingFilename.OpenFile())
                    {
                        Save(fs);
                    }
                    fs.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

            ProgressBarValue = 100;

            MessageBox.Show("Prcoessing Completely!");
        }

        private void btnSaveExprtingFile_Click(object sender, EventArgs e)
        {
            if (sfdExportingFilename.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
                return;

            tbExportingFilename.Text = sfdExportingFilename.FileName; 
        }

        private void Save(System.IO.FileStream fs)
        {
            Byte[] text = new UTF8Encoding(true).GetBytes(tbMessage.Text);
            fs.Write(text, 0, text.Length);  
        }

        private void tbExportingFilename_TextChanged(object sender, EventArgs e)
        {
            //sfdExprtingFilename.FileName = tbExportingFilename.Text;
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}
